package cn.tedu;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcDemo02 {
    static final String URL="jdbc:mysql://localhost:3306/jsdtn2306?serverTimezone=Asia/Shanghai&characterEncoding=utf8";
    static final String username="root";
    static final String password="root";
    static void deleteById(int id){
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接(3次握手)
            conn = DriverManager.getConnection(URL, username, password);
            //3.创建Statement对象
            String sql = "delete from tb_class where id=?";
            pstmt = conn.prepareStatement(sql);
            //4.发送SQL
            pstmt.setInt(1, id);
            boolean flag = pstmt.execute();
            //5.处理结果
            if(!flag) System.out.println("Delete OK,没有结果集返回");
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //6.释放资源
           try{if(pstmt!=null){pstmt.close();pstmt=null;}}catch (SQLException e){}
           try{if(conn!=null){conn.close();conn=null;}}catch (SQLException e){}
        }
    }
    //更新指定id的name值
    static void update(int id,String name){
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接(3次握手)
            conn = DriverManager.getConnection(URL, username, password);
            //3.创建Statement对象
            String sql = "update tb_class set name=? where id=?";
            pstmt = conn.prepareStatement(sql);
            //4.发送SQL
            pstmt.setString(1, name);
            pstmt.setInt(2,id);
            boolean flag = pstmt.execute();
            //5.处理结果
            if(!flag) System.out.println("update OK,没有结果集返回");
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //6.释放资源
            try{if(pstmt!=null){pstmt.close();pstmt=null;}}catch (SQLException e){}
            try{if(conn!=null){conn.close();conn=null;}}catch (SQLException e){}
        }
    }
    //查询tb_class班级表中所有名字以JSDTN开头的记录,每条记录封装一个map对象,多个map存储list集合.
    static List<Map<String,Object>> select(String name){
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接(3次握手)
            conn = DriverManager.getConnection(URL, username, password);
            //3.创建Statement对象
            String sql = "select * from tb_class where name like ?";
            pstmt = conn.prepareStatement(sql);
            //4.发送SQL
            pstmt.setString(1, name+"%");
            boolean flag = pstmt.execute();
            //5.处理结果
            if(!flag)return null;
            //flag为true表示有查询结果
            //5.1获取结果集
            rs=pstmt.getResultSet();
            //5.2迭代结果集
            List<Map<String,Object>> list=new ArrayList<>();
            while(rs.next()){//循环一次取一行(每次调用next都会移动指针)
                    Map<String,Object> map=new HashMap<String,Object>();
                    map.put("id",rs.getInt(1));//读取第一列的值
                    map.put("name",rs.getString(2));
                    map.put("create_time",rs.getDate(3));
                    list.add(map);
                }
            return list;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //6.释放资源
            try{if(rs!=null){rs.close();pstmt=null;}}catch (SQLException e){}
            try{if(pstmt!=null){pstmt.close();pstmt=null;}}catch (SQLException e){}
            try{if(conn!=null){conn.close();conn=null;}}catch (SQLException e){}
        }
    }

    public static void main(String[]args){
        //deleteById(12);
        //update(11,"JSDVN2308");
        select("JSDTN");
    }
}
